详解Mysql中的JSON系列操作函数

一、方法罗列：

分类 函数 描述 创建json json_array 创建json数组 json_object 创建json对象 json_quote 将json转成json字符串类型 查询json json_contains 判断是否包含某个json值 json_contains_path 判断某个路径下是否包json值 json_extract 提取json值 column->path json_extract的简洁写法，MySQL 5.7.9开始支持 column->>path json_unquote(column -> path)的简洁写法 json_keys 提取json中的键值为json数组 json_search 按给定字符串关键字搜索json，返回匹配的路径 修改json json_append 废弃，MySQL 5.7.9开始改名为json_array_append json_array_append 末尾添加数组元素，如果原有值是数值或json对 象，则转成数组后，再添加元素 json_array_insert 插入数组元素 json_insert 插入值（插入新值，但不替换已经存在的旧值） json_merge 合并json数组或对象 json_remove 删除json数据 json_replace 替换值（只替换已经存在的旧值） json_set 设置值（替换旧值，并插入不存在的新值） json_unquote 去除json字符串的引号，将值转成string类型 返回json属性 json_depth 返回json文档的最大深度 json_length 返回json文档的长度 json_type 返回json值得类型 json_valid 判断是否为合法json文档

二、使用例子：

MySQL 5.7.8开始支持 json类型.

create table t(id int,js json,PRIMARY KEY (id))

插入数据 insert into t values(1,'{"a":1,"s":"abc"}') insert into t values(2,'[1,2,{"a":123}]') insert into t values(3,'"str"') insert into t values(4,'123')

直接提供字符串即可。还可以用JSON_Array和JSON_Object函数来构造 insert into t values(5,JSON_Object('key1',v1,'key2',v2)) insert into t values(4,JSON_Array(v1,v2,v3))

JSON_OBJECT([key, val[, key, val] ...]) JSON_ARRAY([val[, val] ...])

JSON_SET(json_doc, path, val[, path, val] ...) 修改数据

结果js={"a":456,"s":"abc","b":"bbb"}

path中.a=456 $.b="bbb" 存在就修改,不存在就设置.

$.c.c=123

这个在javascript中会出错，因为.c为null。 但是在json_set('{}','$.c.c',123)中，不存在的路径将直接被忽略。

特殊的对于数组，如果目标doc不是数组则会被转换成[doc]，然后再执行set， 如果set的下标超过数组长度，只会添加到数组结尾。

结果[{"a":456},123]。目标现被转换成[{"a":456}],然后应用$[1]=123。

select json_set('"abc"','$[999]',123) 结果["abc",123]。

再举几个例子

结果[456,2,3,'bbb']

注意: 对于javascript中 var a=[1,2,3] a.a='abc' 是合法的,但是一旦a转成json字符串,a.a就丢失了。

而在mysql中，这种算作路径不存在，因此 select json_set('[1,2,3]','$.a',456) 结果还是[1,2,3]

然后还有另外两个版本 JSON_INSERT(json_doc, path, val[, path, val] ...) 如果不存在对应属性则插入,否则不做任何变动

JSON_REPLACE(json_doc, path, val[, path, val] ...) 如果存在则替换,否则不做任何变动

这两个操作倒是没有javascript直接对应的操作

加上删除节点 JSON_REMOVE(json_doc, path[, path] ...) 如果存在则删除对应属性,否则不做任何变动 select json_replace('{"a":1,"s":"abc"}','.b') 结果{"s":"abc"}

涉及数组时，三个函数与json_set基本一样 select json_insert('{"a":1}','$[0]',456) 结果不变，认为0元素已经存在了，注意这里结果不是[{"a":1}]

select json_insert('{"a":1}','$[999]',456) 结果追加到数组结尾[{"a":1}，456]

select json_replace('{"a":1}','$[0]',456) 结果456！而非[456]

select json_replace('{"a":1}','$[1]',456) 结果不变。

其实对于json_insert和json_replace来说一般情况没必要针对数组使用。

select json_remove('{"a":1}','$[0]') 结果不变！

select json_remove('[{"a":1}]','$[0]') 结果[]

总之涉及数组的时候要小心。

JSON_MERGE(json_doc, json_doc[, json_doc] ...) 将多个doc合并

select json_merge('[1,2,3]','[4,5]') 结果[1,2,3,4,5]。数组简单扩展

select json_merge('{"a":1}','{"b":2}') 结果{"a":1,"b":2}。两个对象直接融合。

特殊的还是在数组 select json_merge('123','45') 结果[123,45]。两个常量变成数组

select json_merge('{"a":1}','[1，2]') 结果[{"a":1},1,2]。目标碰到数组，先转换成[doc]

select json_merge('[1,2]','{"a":1}') 结果[1,2,{"a":1}]。非数组都追加到数组后面。

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) 给指定的节点，添加元素，如果节点不是数组，则先转换成[doc]

select json_Array_append('[1,2]','/article>,'456') 结果[1,2,456]

结果[[1,456],2]。指定插在[0]','456')

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) 在数组的指定下标处插入元素

提取json信息的函数 JSON_KEYS(json_doc[, path]) 返回指定path的key

select json_keys('{"a":1,"b":2}') 结果["a","b"]

select json_keys('{"a":1,"b":[1,2,3]}','$.b') 结果null。数组没有key

JSON_CONTAINS(json_doc, val[, path]) 是否包含子文档

select json_contains('{"a":1,"b":4}','{"a":1}') 结果1

select json_contains('{"a":2,"b":1}','{"a":1}') 结果0

select json_contains('{"a":[1,2,3],"b":1}','[1,2]','$.a') 结果1。数组包含则需要所有元素都存在。

select json_contains('{"a":[1,2,3],"b":1}','1','$.a') 结果1。元素存在数组元素中。

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) 检查路径是否存在

select JSON_CONTAINS_PATH('{"a":1,"b":{"c":{"d":1}}}', 'one','$.b.c.d') 结果1。

select JSON_CONTAINS_PATH('{"a":1,"b":{"c":{"d":1}}}', 'one','$.a.c.d') 结果0。

JSON_EXTRACT(json_doc, path[, path] ...) 获得doc中某个或多个节点的值。

select json_extract('{"a":1,"b":2}','$.a') 结果1

select json_extract('{"a":[1,2,3],"b":2}','$.a[1]') 结果2

select json_extract('{"a":{"a":1,"b":2,"c":3},"b":2}','$.a.') 结果[1,2,3]。a.通配a所有属性的值返回成数组。

参考： http://www.jb51.net/article/89219.htm http://blog.csdn.net/joker_honey/article/details/53081716

原文： https://blog.csdn.net/jiangyu1013/article/details/78917425